const sql = require('mssql');
const baseFn = require('../../public/base.js');

// 获取拼接的公司，部门的名称
const getJoinName = async (val) => {
    let companyResult = await sql.query(`SELECT STUFF((SELECT '-'+name FROM GSGL WHERE id in(${val}) FOR XML PATH('')),1,1,'') AS company`);
    return companyResult.recordset[0].company;
}
// 获取所有员工信息
const getPayRiseInfo = async (ctx, next) => {
    let getBody = ctx.request.body;
    let searchList = getBody.searchList;
    let selectSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY J.id) AS PX,J.id,J.money,J.precent,J.riseTime,J.reason,J.applyTime,J.status,J.leaderComment,D.username,D.part FROM YG_JX J LEFT JOIN YG_DA D ON J.userId = D.id `;
    let totalCountSql = `SELECT COUNT(J.id) AS TOTAL FROM YG_JX J LEFT JOIN YG_DA D ON J.id = D.id `;
    if (searchList.username) {
        selectSql += ` WHERE (D.username LIKE '%${searchList.username}%' OR D.phone LIKE '%${searchList.username}%') `;
        totalCountSql += ` WHERE (D.username LIKE '%${searchList.username}%' OR D.phone LIKE '%${searchList.username}%') `;
    }
    if (0 in searchList.part) {
        selectSql += selectSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        selectSql += ` D.job LIKE '%${searchList.part.join(',')}%' `;
        totalCountSql += totalCountSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        totalCountSql += ` D.job LIKE '%${searchList.part.join(',')}%' `;
    }
    if (searchList.idNum) {
        selectSql += selectSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        selectSql += ` D.idnum LIKE '%${searchList.idNum}%' `;
        totalCountSql += totalCountSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        totalCountSql += ` D.idnum LIKE '%${searchList.idNum}%' `;
    }
    if (searchList.entryTime) {
        selectSql += selectSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        selectSql += ` D.entrytime >= '${searchList.entryTime[0]}' AND D.entrytime <= '${searchList.entryTime[1]}' `;
        totalCountSql += totalCountSql.indexOf('WHERE') === -1 ? 'WHERE' : 'AND'
        totalCountSql += ` D.entrytime >= '${searchList.entryTime[0]}' AND D.entrytime <= '${searchList.entryTime[1]}' `;
    }
    selectSql += `) AS T1 WHERE T1.PX BETWEEN ${(getBody.pageIndex - 1) * (getBody.pageSize) + 1} AND ${getBody.pageIndex * (getBody.pageSize)} `;
    let result = await sql.query(selectSql);
    let total = await sql.query(totalCountSql);
    for (let item of result.recordset) {
        item.partName = await getJoinName(item.part);
    };
    ctx.body = { result: result.recordset, total: total.recordset[0].TOTAL };
}
// 提交加薪审批
const addPayRiseAudit = async (ctx, next) => {
    let getBody = ctx.request.body;
    let upDateSql = `UPDATE YG_JX SET leaderComment = '${getBody.reason}',auditTime = '${baseFn.getNowFormatDate()}',status = 'T' WHERE id= ${getBody.userId} `;
    let upInfo = await sql.query(`SELECT money,userId FROM YG_JX WHERE id = ${getBody.userId}`);
    let result = await sql.query(upDateSql);
    let upInfoResult = upInfo.recordset[0];
    let oldMoney = await sql.query(`SELECT realWage FROM YG_DA WHERE id =${upInfoResult.userId}`);
    let nowMoney = Number(oldMoney.recordset[0].realWage) + Number(upInfoResult.money);

    let updataUserInfoSql = `UPDATE YG_DA SET realWage = '${nowMoney}' WHERE id =${upInfoResult.userId} `;
    await sql.query(updataUserInfoSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = {
            success: true
        }
    } else {
        ctx.body = {
            success: false
        }
    }
}


module.exports = {
    // 'POST /keepEntryRegistration': keepEntryRegistration,
    // 'POST /getEntryRegistrationInfo': getEntryRegistrationInfo,
    // 'POST /getEditEntryInfo': getEditEntryInfo,
    // 'POST /getEntryPicAdress': getEntryPicAdress,
    // 'POST /addEmployeeUpInfo': addEmployeeUpInfo,
    // 'POST /addPayRise': addPayRise,
    'POST /getPayRiseInfo': getPayRiseInfo,
    'POST /addPayRiseAudit': addPayRiseAudit



}